# -*- coding:utf-8 _*-  
""" 
@author:yanking
@file: home_1.py
@version:
@time: 2020/07/26 
@email:476588294@qq.com
@function： 
"""

import re

import pandas as pd
import numpy as np
from PyQt5.QtGui import QPixmap, QFont, QCursor, QColor, QResizeEvent
from PyQt5.QtWidgets import QMainWindow, QMessageBox, QDesktopWidget, QAbstractItemView, QTableWidgetItem, qApp, \
    QHeaderView, QFileDialog
from PyQt5.QtCore import Qt, pyqtSlot, QEvent
from openpyxl.utils import get_column_letter
from openpyxl import load_workbook, styles
from pandas import DataFrame

from src.database import DataBase
from src.window import Ui_MainWindow
from src.setting_interface import Setting
import src.config as conf
from src.utils import get_user_info


# 设置excel文件的样式
def set_excel_style(path, sheet, width_list):
    wb = load_workbook(path)
    ws = wb[sheet]
    border = styles.Border(left=styles.Side(border_style='thin', color='000000'),
                           right=styles.Side(border_style='thin', color='000000'),
                           top=styles.Side(border_style='thin', color='000000'),
                           bottom=styles.Side(border_style='thin', color='000000'))

    for index, width in enumerate(width_list):
        ws.column_dimensions[get_column_letter(index + 1)].width = width
    for row in ws.rows:
        for cell in row:
            cell.alignment = styles.Alignment(horizontal='left', vertical='center', wrapText=False)
            cell.border = border
    wb.save(path)
    wb.close()


class SetupUi(QMainWindow, Ui_MainWindow):
    def __init__(self):
        super(SetupUi, self).__init__()
        self.setupUi(self)
        self.current_text = ""
        self.header = conf.header
        self.table = conf.pdms_table_name

        self.header_ch_en = {value: key for key, value in conf.header.items()}

        self.toolButtonSearch_dic = {self.le_zj: "zj", self.le_bj: "bj", self.le_zpc1: "zpc1", self.le_zpc2: "zpc2",
                                     self.le_mc: "mc", self.le_gg: "gg", self.le_xh: "xh", self.le_wh: "wh",
                                     self.le_fz: "fz", self.le_sl: "sl", self.le_cj: "cj", self.le_es: "es",
                                     self.le_zt: "zt", self.le_bz: "bz", self.le_wjmc: "wjmc"}

        # self.le_bj.setText('00-0001')
        # 权限设置
        self.le_zj.setFocus()
        self.set_authorization()

        self.center()
        self.init_ui()

    # cell 更改后保存数据
    def cell_changed(self, current_row, current_col, previous_row, previous_col):
        if previous_row == previous_col == -1 or current_row == current_col == -1:
            return
        changed_value = self.tableWidget.item(previous_row, previous_col).text()
        if self.current_text != changed_value:

            update_key = list(self.toolButtonSearch_dic.values())[previous_col]
            current_text = [changed_value]

            conditions = list(map(lambda x: x + "=%s", self.toolButtonSearch_dic.values()))

            for i in range(self.tableWidget.columnCount()):
                if i == previous_col:
                    current_text.append(self.current_text)
                else:
                    current_text.append(self.tableWidget.item(previous_row, i).text())

            sql = "update {} set {}=%s  where {}".format(self.table, update_key, " AND ".join(conditions))
            connect = DataBase(self.header, conf.user_table_name)
            cur, db = connect.get_connect()
            cur.execute(sql, current_text)
            db.commit()
            db.close()

    # 设置权限函数
    def set_authorization(self):
        hostname, ip, mac = get_user_info()
        connect = DataBase(self.header, conf.user_table_name)
        cur, db = connect.get_connect()
        sql = 'SELECT authorization,user FROM user  where hostname=%s AND ip=%s AND mac=%s'
        cur.execute(sql, [hostname, ip, mac])
        result = cur.fetchall()
        db.close()
        if result:
            # 当前权限为管理权限
            if result[0][0] == conf.authorization_list[0]:
                self.clb_down.setDisabled(False)
                self.clb_purchase.setDisabled(False)
                # self.tableWidget.setEditTriggers(QAbstractItemView.EditTriggers)

            # 当前为下载权限
            elif result[0][0] == conf.authorization_list[1]:
                self.clb_down.setDisabled(False)
                self.clb_purchase.setDisabled(False)
                self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)

            # 其他的默认为查看权限
            else:
                self.clb_down.setDisabled(True)
                self.clb_purchase.setDisabled(True)
                self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)

            if result[0][1] and result[0][1] != "None":
                self.labelUserInfo.setText(result[0][1] + '({}) 欢迎您！'.format(result[0][0]))
            else:
                self.labelUserInfo.setText(hostname + '({}) 欢迎您！'.format(result[0][0]))

        else:
            self.clb_down.setDisabled(True)
            self.clb_purchase.setDisabled(True)
            self.labelUserInfo.setText(hostname + '({}) 欢迎您！'.format(conf.authorization_list[0]))

    # 关闭窗口事件
    def closeEvent(self, event) -> None:
        reply = QMessageBox.question(self, 'PDMs', "请确定是否要退出PDMs?", QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
        if reply == QMessageBox.Yes:
            event.accept()
        else:
            event.ignore()

    # 打开界面存放在屏幕中间
    def center(self):
        qr = self.frameGeometry()
        cp = QDesktopWidget().availableGeometry().center()
        qr.moveCenter(cp)
        self.move(qr.topLeft())

    # 初始化界面
    def init_ui(self):
        # self.label_logo.setPixmap(QPixmap(":/icon/icon/logo.ico"))
        self.label_pdms.setToolTip("Product Data Management System")
        self.tableWidget.setHidden(True)
        self.label.setHidden(False)
        self.label.setPixmap(QPixmap(':/icon/icon/welcome.png'))
        self.label.setScaledContents(True)
        self.tableWidget.setTabKeyNavigation(False)
        self.set_authorization()
        self.clb_down.setDisabled(True)
        self.clb_purchase.setDisabled(True)

        # 全程可以使用的槽函数
        self.clb_help.clicked.connect(self.click_about)
        self.tableWidget.cellClicked.connect(self.on_clicked_cell)

        # 状态栏设置
        self.statusbar.setStyleSheet("color:#0000FF")
        self.statusbar.showMessage("欢迎使用产品数据管理系统...")

        # 初始化子项父项的可用状态
        self.pushButtonFarther.setDisabled(True)
        self.pushButtonSon.setDisabled(True)

        # 默认设置表格文字不可编辑状态
        # self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
        # 表格背景设置成间隔显示
        self.tableWidget.setAlternatingRowColors(True)
        # 设置表头自动填充模式
        font = QFont()
        font.setBold(True)
        font.setPointSize(11)
        self.tableWidget.horizontalHeader().setFont(font)
        self.tableWidget.horizontalHeader().setStyleSheet('QHeaderView::section{background:#f0f0f0;}')
        # 设置表格默认的鼠标为箭头形式
        self.tableWidget.setCursor(QCursor(Qt.ArrowCursor))

        self.write_user_info()

    # 自动获取电脑信息并写入数据库
    def write_user_info(self):
        hostname, ip, mac = get_user_info()
        header = ['id', 'hostname', 'ip', 'mac', 'note']
        con = DataBase(header, conf.user_table_name)
        cursor, db = con.get_connect()
        sql = 'SELECT user,hostname,ip,mac,authorization,note FROM user'
        cursor.execute(sql)
        result = cursor.fetchall()

        if len(result) == 0:
            sql = "INSERT INTO user (hostname, ip, mac,authorization) VALUES (%s, %s, %s, %s)"
            try:
                cursor.execute(sql, [hostname, ip, mac, conf.authorization_list[0]])
                db.commit()
            except Exception as error:
                self.statusbar.setStyleSheet("color:#FF0000")
                self.statusbar.showMessage('写入用户信息出错：%s' % error)
            finally:
                db.close()
        else:
            sql = 'SELECT authorization,user FROM user  where hostname=%s AND ip=%s AND mac=%s'
            cursor.execute(sql, [hostname, ip, mac])
            result = cursor.fetchall()
            if not result:
                sql = "INSERT INTO user (hostname, ip, mac,authorization) VALUES (%s, %s, %s, %s)"
                try:
                    cursor.execute(sql, [hostname, ip, mac, conf.authorization_list[2]])
                    db.commit()
                except Exception as error:
                    self.statusbar.setStyleSheet("color:#FF0000")
                    self.statusbar.showMessage('写入用户信息出错：%s' % error)
                finally:
                    db.close()
            else:
                db.close()

    # 帮助按钮槽函数
    def click_about(self):
        header = ['user', 'ip']
        con = DataBase(header, conf.user_table_name)
        cursor, db = con.get_connect()
        sql = 'SELECT hostname,user,ip FROM user  where authorization="管理权限"'
        cursor.execute(sql)
        result = cursor.fetchall()
        managers = []
        for item in result:
            if item[1]:
                managers.append('{}({})'.format(item[1], item[-1]))
            else:
                managers.append('{}({})'.format(item[0], item[-1]))
        managers = ';'.join(managers)

        info ='<font color=#FA4C35 size=4> %s </font>' % """
        <p> 版本号：{0} </p>                         
        <p>管理员：{1}</p> 
        <p>本软件版权归山西支点科技有限公司所有,未经授权,严禁传播！</p> 
            """.format(conf.version_, managers)
        QMessageBox.about(self, 'PDMs帮助中心', info)


# 主页类
class HomePage(SetupUi):
    def __init__(self):
        super(HomePage, self).__init__()
        self.db = DataBase(self.header, self.table)
        self.clb_setting.clicked.connect(self.on_clicked_clb_setting)

    # 设置按钮点击事件
    @staticmethod
    def on_clicked_clb_setting():
        setting = Setting()
        setting.show()
        return setting.exec()

    # 窗口大小触发事件改写
    def resizeEvent(self, a0: QResizeEvent) -> None:
        self.stretch_table()

    # 表格列标题自适应变化
    def stretch_table(self):
        cols = self.tableWidget.columnCount()
        if cols:
            width = self.tableWidget.width() - 30
            for i in range(cols):
                self.tableWidget.horizontalHeader().resizeSection(i, int(width * conf.radio[i]))
                # print(i, int(width * conf.radio[i]))

    # 填充表格数据
    def fill_table(self, data):
        """
        :param keys: 查询的关键字列表
        :param data: 数据库查询到的数据
        :return:
        """
        header = list(self.header.values())
        self.tableWidget.clearContents()
        self.tableWidget.setRowCount(len(data))
        self.tableWidget.setColumnCount(len(header))
        self.tableWidget.setHorizontalHeaderLabels(header)
        self.stretch_table()
        self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)
        self.tableWidget.horizontalHeader().setCascadingSectionResizes(True)
        self.clb_down.setDisabled(True)
        rows = len(data)

        for i in range(rows):
            for j in range(len(data[i])):
                if j == len(data[i]):
                    continue
                item = QTableWidgetItem(data[i][j])
                self.tableWidget.setItem(i, j, item)
            else:
                text = "数据查询中，请稍等... {0}/{1} -({2}%)".format(i, rows, int(i * 100 / rows))
                self.statusbar.setStyleSheet("color:#0000FF")
                self.statusbar.showMessage(text)
                qApp.processEvents()
        else:
            text = "查询完成，共查询到 {} 条数据".format(rows)
            self.statusbar.setStyleSheet("color:#0000FF")
            self.statusbar.showMessage(text)
            self.clb_down.setDisabled(False)
            qApp.processEvents()

    # 数据库搜索
    def db_search(self, sql):
        try:
            cur, db = self.db.get_connect()
            cur.execute(sql)
            data = cur.fetchall()
            cur.close()
            return data
        except Exception as error:
            self.statusbar.setStyleSheet("color:#FF0000")
            self.statusbar.showMessage('查询数据时出错：{}'.format(error))

    # 获取搜索关键字
    def get_search_keys(self):
        result = ""
        for key, value in self.toolButtonSearch_dic.items():
            key_value = key.text()
            if key_value == "":
                continue
            if "*" in key_value:
                key_value = re.sub("\\*", "%", key_value)
                if result:
                    result += "AND {} LIKE '{}'".format(value, key_value)
                else:
                    result = "{} LIKE '{}'".format(value, key_value)
            else:
                if result:
                    result += "AND {} = '{}'".format(value, key_value)
                else:
                    result = "{} = '{}'".format(value, key_value)
        return result

    # 单元格点击事件
    def on_clicked_cell(self):

        if self.tableWidget.currentItem():
            current_title = self.tableWidget.horizontalHeaderItem(self.tableWidget.currentColumn()).text()
            # 当前点击的为整件 --> 子项 可见
            if current_title == '整件':
                self.pushButtonFarther.setDisabled(True)
                self.pushButtonSon.setDisabled(False)
                self.tableWidget.setCursor(QCursor(Qt.ArrowCursor))

            # 当前点击的为部件，装配处 --> 子项，父项 可见
            elif current_title == '部件' or current_title == '装配处1' or current_title == '装配处2':
                self.pushButtonFarther.setDisabled(False)
                self.pushButtonSon.setDisabled(False)
                self.tableWidget.setCursor(QCursor(Qt.ArrowCursor))

            # 当前点击单元格内容为 ... --> 子项，父项 不可见
            elif self.tableWidget.currentItem().text() == '...':
                self.pushButtonFarther.setDisabled(True)
                self.pushButtonSon.setDisabled(True)
                self.tableWidget.setCursor(QCursor(Qt.ArrowCursor))

            # 物料编码，规格，型号  --> 仅父项 可见
            elif current_title == '物料编码' or current_title == '规格' or current_title == '型号':
                self.pushButtonFarther.setDisabled(False)
                self.pushButtonSon.setDisabled(True)
                self.tableWidget.setCursor(QCursor(Qt.ArrowCursor))

            # 其他 --> 仅父项 可见
            else:
                self.pushButtonFarther.setDisabled(True)
                self.pushButtonSon.setDisabled(True)
                self.tableWidget.setCursor(QCursor(Qt.ArrowCursor))

            # 状态栏显示当前数据
            self.statusbar.setStyleSheet("color:#0000FF")
            self.statusbar.showMessage(current_title + "-->" + self.tableWidget.currentItem().text())
        else:
            self.statusbar.setStyleSheet("color:#0000FF")
            self.statusbar.showMessage('未选中单元格数据')

    # 使用fill_value填充item_list列，并去重返回数据
    def fix_data(self, df, item_list, fill_value):

        df = DataFrame(df, columns=list(self.header.values())[0:len(item_list)])
        df[item_list] = fill_value
        df_drop = df.drop_duplicates()
        return df_drop.values

    # 父项按钮事件
    @pyqtSlot()
    def on_pushButtonFarther_clicked(self):
        action_y = self.tableWidget.currentColumn()
        action_x = self.tableWidget.currentRow()
        header_keys = list(conf.header.keys())
        if action_y == -1 or action_x == -1:
            return
        # 规格和型号的父项需要与封装互相关联
        elif action_y == 6 or action_y == 7:
            text_6 = self.tableWidget.item(action_x, 6).text()
            text_7 = self.tableWidget.item(action_x, 7).text()
            text_9 = self.tableWidget.item(action_x, 9).text()
            key_6, key_7, key_9 = header_keys[6], header_keys[7], header_keys[9]
            sql_keys = "{} = '{}' AND {} = '{}' AND {} = '{}'".format(key_6, text_6, key_7, text_7, key_9, text_9)

            fill_list = [header_keys[5]] + [header_keys[8]] + header_keys[10:None]
            sql = "SELECT {} from {}  where {}".format(','.join(header_keys), self.table, sql_keys)
            data = self.db_search(sql)
            df = pd.DataFrame(data, columns=header_keys)
            df[fill_list] = '...'
            df_drop = df.drop_duplicates().values
            self.fill_table(df_drop)
            self.pushButtonFarther.setDisabled(True)

        # 部件，装配处1和2，器件编码的父项，需要筛选唯一值
        elif action_y <= 4:
            text = self.tableWidget.item(action_x, action_y).text()
            sql_keys = "{} = '{}'".format(header_keys[action_y], text)
            fill_list = ','.join(header_keys[:action_y + 1])
            # 数据查询
            sql = "SELECT {} from {}  where {}".format(fill_list, self.table, sql_keys)
            data = self.db_search(sql)
            fill_list = [list(self.header.values())[i] for i in range(action_y + 1, len(conf.header))]
            cols_title = list(self.header.values())[0:action_y + 1]
            df = DataFrame(data, columns=cols_title)
            df[fill_list] = '...'
            df_drop = df.drop_duplicates().values
            self.fill_table(df_drop)
            self.pushButtonFarther.setDisabled(True)
        else:
            self.statusbar.setStyleSheet("color:#FF0000")
            self.statusbar.showMessage("数据有误，请核查！！")

    # 子项按钮事件
    @pyqtSlot()
    def on_pushButtonSon_clicked(self):
        action_y = self.tableWidget.currentColumn()
        action_x = self.tableWidget.currentRow()
        sql_keys = ""
        header_keys = list(conf.header.keys())
        if action_y == -1 or action_x == -1:
            return

        for i in range(0, action_y + 1):
            text = self.tableWidget.item(action_x, i).text()
            key = header_keys[i]
            if i == 0:
                sql_keys = " {} = '{}'".format(key, text)
            else:
                sql_keys += "AND {} = '{}'".format(key, text)
        sql = "select {} from {} where {}".format(','.join(header_keys), self.table, sql_keys)
        son_data = self.db_search(sql)

        if not son_data:
            self.statusbar.setStyleSheet("color:#FF0000")
            self.statusbar.showMessage("查询结果为空，请核对搜索关键字是否正确")
        self.fill_table(son_data)

        self.pushButtonSon.setDisabled(True)

    # 搜索按钮事件
    @pyqtSlot()
    def on_toolButtonSearch_clicked(self):
        self.label.setHidden(True)
        self.tableWidget.setHidden(False)
        self.toolButtonSearch.setDisabled(True)

        sql_keys = self.get_search_keys()
        keys = ",".join(list(self.header.keys()))

        if sql_keys is None:
            self.fill_table(data=[])
            self.statusbar.setStyleSheet("color:#FF0000")
            self.statusbar.showMessage("搜索数据有误，请输入正确的搜索关键字")
        else:
            sql = "select {} from {}  where {}".format(keys, self.table, sql_keys)
            data = self.db_search(sql)

            if not data:
                self.statusbar.setStyleSheet("color:#FF0000")
                self.statusbar.showMessage("查询结果为空，请核对搜索关键字是否正确")
                self.clb_down.setDisabled(True)
                self.clb_purchase.setDisabled(True)
                self.fill_table(data=[])
            else:
                self.fill_table(data)
                self.clb_down.setDisabled(False)
                self.clb_purchase.setDisabled(False)
        self.toolButtonSearch.setDisabled(False)
        self.le_zj.setFocus()

    # 下载按钮事件
    @pyqtSlot()
    def on_clb_down_clicked(self):
        sheet_name = '查询结果'
        path, _ = QFileDialog.getSaveFileName(self, '请选择保存路径', sheet_name, '*.xlsx')
        if path:
            try:
                header = [self.tableWidget.horizontalHeaderItem(i).text() for i in
                          range(self.tableWidget.columnCount())]

                result = []
                for i in range(self.tableWidget.rowCount()):
                    cols = []
                    for j in range(self.tableWidget.columnCount()):
                        text = self.tableWidget.item(i, j).text()
                        cols.append(text)
                    result.append(cols)
                df = DataFrame(result, columns=header)
                if not df.empty:
                    df.to_excel(path, sheet_name=sheet_name)
                    width_list = [5, 10, 10, 10, 10, 10, 10, 20, 20, 30, 10, 10, 10, 10, 10, 10]
                    set_excel_style(path, sheet_name, width_list)
                    self.statusbar.setStyleSheet("color:#0000FF")
                    self.statusbar.showMessage('文件下载完成，保存路径为：' + path)
                else:
                    self.statusbar.setStyleSheet("color:#0000FF")
                    self.statusbar.showMessage('当前界面数据为空，请确认查询条件')
            except Exception as error:
                self.statusbar.setStyleSheet("color:#FF0000")
                self.statusbar.showMessage('文件下载失败，错误码：{}'.format(error))
        else:
            self.statusbar.setStyleSheet("color:#0000FF")
            self.statusbar.showMessage('您已经取消数据下载！')

    @pyqtSlot()
    def on_clb_purchase_clicked(self):
        sheet_name = '采购清单'
        path, _ = QFileDialog.getSaveFileName(self, '请选择保存路径', sheet_name, '*.xlsx')
        if path:
            columns = [5, 6, 7, 9, 10]
            header = [self.tableWidget.horizontalHeaderItem(i).text() for i in columns]
            result = []
            for i in range(self.tableWidget.rowCount()):
                cols = []
                for j in columns:
                    text = self.tableWidget.item(i, j).text()
                    cols.append(text)
                result.append(cols)
            df = DataFrame(result, columns=header)
            try:
                df[header[-1]] = df[header[-1]].astype(int)
                if not df.empty:
                    grouped = df.groupby(header[:-1]).agg(np.sum)
                    grouped.to_excel(path, sheet_name=sheet_name)
                    width_list = [20, 20, 20, 10]
                    set_excel_style(path, sheet_name, width_list)
                    self.statusbar.setStyleSheet("color:#0000FF")
                    self.statusbar.showMessage('文件下载完成，保存路径为：' + path)
                else:
                    self.statusbar.setStyleSheet("color:#0000FF")
                    self.statusbar.showMessage('当前界面数据为空，请确认查询条件')
            except ValueError as error:
                self.statusbar.setStyleSheet("color:#FF0000")
                self.statusbar.showMessage('字符串转化数字错误，请确认【数量】列是否含有非数字字符！！{}'.format(error))
        else:
            self.statusbar.setStyleSheet("color:#0000FF")
            self.statusbar.showMessage('您已经取消数据下载！')

    # 清空按钮事件
    @pyqtSlot()
    def on_clb_clear_clicked(self):
        self.fill_table(data=[])

        self.le_bj.clear()
        self.le_zpc1.clear()
        self.le_zpc2.clear()
        self.le_gg.clear()
        self.le_mc.clear()
        self.le_sl.clear()
        self.le_wh.clear()
        self.le_bz.clear()
        self.le_cj.clear()
        self.le_es.clear()
        self.le_fz.clear()
        self.le_wjmc.clear()
        self.le_wlbm.clear()
        self.le_zt.clear()
        self.le_xh.clear()
        self.le_zj.clear()

    @pyqtSlot()
    def on_clb_home_clicked(self):
        self.label.setHidden(False)
        self.tableWidget.setHidden(True)
